﻿Imports System.Data
Imports System.Data.OleDb
Imports DTO

Public Class TuyenDao
    Dim provider As New DataProvider
    Public Function LayDanhSachTuyen() As DataTable
        Dim sql As String = "SELECT * FROM TuyenXe"
        Dim dtTUYENXE As DataTable
        dtTUYENXE = provider.ThucThiCauTruyVan(sql)
        Return dtTUYENXE
    End Function
    
    Public Function TimTuyen(ByVal tuyen As TuyenDto) As DataTable
        Dim sql As String = "SELECT * FROM TuyenXe WHERE 1"
        'If tuyen.MaTuyenXe <> 0 Then
        '    sql += " and MaTuyen =" & tuyen.MaTuyenXe & " "
        'End If
        If tuyen.TenTuyenXe <> "" Then
            sql += " and TenTuyen = '" & tuyen.TenTuyenXe & "' "
        End If
        If tuyen.DiaDiemDi <> "" Then
            sql += " and DiaDiemDi = '" & tuyen.DiaDiemDi & "' "
        End If
        If tuyen.DiaDiemDen <> "" Then
            sql += " and DiaDiemDen = '" & tuyen.DiaDiemDen & "' "
        End If

        Return provider.ThucThiCauTruyVan(sql)
    End Function
    Public Function TimTuyenTheoMaTuyen(ByVal maTuyen As Integer) As DataTable
        Dim sql As String = "SELECT * FROM TuyenXe WHERE MaTuyen = " & maTuyen

        Dim dtTUYEN As DataTable
        dtTUYEN = provider.ThucThiCauTruyVan(sql)

        Return dtTUYEN
    End Function


    Public Function ThemTuyen(ByVal tuyen As TuyenDto) As Integer
        Dim sql As String = "INSERT INTO TuyenXe(TenTuyen, DiaDiemDi, DiaDiemDen) VALUES (?, ? ,?)"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@TenTuyen", tuyen.TenTuyenXe))
        dsParameter.Add(New OleDbParameter("@DiaDiemDi", tuyen.DiaDiemDi))
        dsParameter.Add(New OleDbParameter("@DiaDiemDen", tuyen.DiaDiemDen))

        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function CapNhatTuyen(ByVal tuyen As TuyenDto) As Integer
        Dim sql As String = "UPDATE TuyenXe SET TenTuyen = ?, DiaDiemDi = ?, DiaDiemDen = ? WHERE MaTuyen = ?"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@TenTuyen", tuyen.TenTuyenXe))
        dsParameter.Add(New OleDbParameter("@DiaDiemDi", tuyen.DiaDiemDi))
        dsParameter.Add(New OleDbParameter("@DiaDiemDen", tuyen.DiaDiemDen))
        dsParameter.Add(New OleDbParameter("@MaTuyenXe", tuyen.MaTuyenXe))
        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function XoaTuyen(ByVal maCanXoa As Long) As Integer
        Dim sql As String = "DELETE FROM TuyenXe WHERE MaTuyen = ?"

        Dim dsParameter As New List(Of OleDbParameter)
        dsParameter.Add(New OleDbParameter("@MaTuyen", maCanXoa))
        Return provider.ThucThiTruyVanThuong(sql, dsParameter)
    End Function

    Public Function CapNhatXuongCSDL(ByVal tenBang As String, ByVal dt As DataTable) As Integer
        Return provider.CapNhatXuongCSDL(tenBang, dt)
    End Function
End Class


